package com.dp.service.impl3;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.JsonPage;
import com.dp.util.DataMap;
import com.dp.vo.NewsMsgTemplate;
import com.dp.vo.NewsSnmsg;
import com.dp.vo.NewsSnroom;

/**
 * 
 * 项目名称：platform_p2
 * 类名称：MessagesendServiceImpl3
 * 类描述：酒店客房信息发布service
 * 创建人：Administrator
 * 创建时间：2010-10-19 15:29:40
 * 修改人：Administrator
 * 修改时间：2010-10-19 15:29:40
 * 修改备注：
 * @version 
 * 
 */
@Service
@Transactional
public class MessagesendServiceImpl3 extends BaseImpl {
	
	public int findAllNewsMsgTemplateCount(DataMap hashMap) {
		String roleid = (String)hashMap.get("roleid");
		String userid = (String)hashMap.get("userid");
		String spcodeS = (String)hashMap.get("spcodeS");
		String spcode = (String)hashMap.get("spcode");
		String templatename = (String)hashMap.get("templatename");
		String templatemsg = (String)hashMap.get("templatemsg");
		 sql = "SELECT count(id) FROM news_msgtemplate where 1=1 ";
		if (roleid==null&&!"root".equals(userid)) {
			sql += " and spcode in (" + spcodeS + ") ";
		}else if (roleid != null) {
			sql += " and spcode = '" + spcode + "' ";
		}
		if (templatename != null && !"".equals(templatename)) {
			sql += " and templatename like '%" + templatename + "%'";
		}
		if (templatemsg != null && !"".equals(templatemsg)) {
			sql += " and templatemsg like '%" + templatemsg + "%'";
		}
		int count = 0;
		try {
			log.info(" findAllNewsMsgTemplateCount excute sql: " +sql);
			count = this.getOneInt();
		} catch (Exception e) {
			log.info(" findAllNewsMsgTemplateCount fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return (count);
	}
	
	public List<NewsMsgTemplate> findAllNewsMsgTemplate(DataMap hashMap, int firstrow, int maxrow) {
		JsonPage jpage=(JsonPage)hashMap.get("jpage");
		String roleid = (String)hashMap.get("roleid");
		String userid = (String)hashMap.get("userid");
		String spcodeS=(String)hashMap.get("spcodeS");
		String spcode = (String)hashMap.get("spcode");
		String templatename = (String)hashMap.get("templatename");
		String templatemsg = (String)hashMap.get("templatemsg");
		String sql = "SELECT id,templatename,templatemsg FROM news_msgtemplate where 1=1 ";
		if (roleid==null&&!"root".equals(userid)) {
			sql += " and spcode in (" + spcodeS + ")";
		}else if (roleid != null) {
			sql += " and spcode = '" + spcode + "' ";
		}
		if (templatename != null && !"".equals(templatename)) {
			sql += " and templatename like '%" + templatename + "%'";
		}
		if (templatemsg != null && !"".equals(templatemsg)) {
			sql += " and templatemsg like '%" + templatemsg + "%'";
		}
		if (jpage != null && jpage.getSidx() != null
				&& !"".equals(jpage.getSidx())) {
			sql += " order by " + jpage.getSidx();
			sql += " " + jpage.getSord();
		}
		sql += " limit " +firstrow+","+maxrow;
		List<NewsMsgTemplate> result = new ArrayList<NewsMsgTemplate>();
		try {
			
			log.info(" findAll NewsMsgTemplate excute sql: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				NewsMsgTemplate one = new NewsMsgTemplate();
				one.setId(this.getInteger(o[0]));
				one.setTemplatename(this.getString(o[1]));
				one.setTemplatemsg(this.getString(o[2]));
				result.add(one);
			}
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean updateNewsMsgtemplate(NewsMsgTemplate newsMsgtemplate)
	{
        String sql = "update news_msgtemplate set templatename = '"+newsMsgtemplate.getTemplatename()+"', templatemsg = '"+newsMsgtemplate.getTemplatemsg()+"', templatepicture = '"+
        				newsMsgtemplate.getTemplatepicture()+"' where id ="+newsMsgtemplate.getId();
        log.info(" update NewsMsgTemplate excute sql :" +sql);
		try {
			return this.execUpdate(sql);
		} catch (Exception e) {
			log.info(" update NewsMsgTemplate fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean updateNewsSnroom(NewsSnroom newsSnroom, String oldSn)
	{
        String sql = "update news_snroom set sn = '"+newsSnroom.getSn()+"', roomnumber = '"+newsSnroom.getRoomnumber()+"', telphone = '"+
        				newsSnroom.getTelphone()+"', rfloor = '"+newsSnroom.getRfloor()+"' where sn = '"+oldSn+"'";
        log.info(" update NewsSnroom excute sql :" +sql);
		try {
			return this.execUpdate(sql);
		} catch (Exception e) {
			log.info(" update NewsSnroom fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean checkSnroom(NewsSnroom snroom)
	{
        String sql = "select sn from news_snroom where sn = '"+snroom.getSn()+"'";
		log.info(" checkSnroom excute sql :" +sql);
		try {
		
				return true;
		
		} catch (Exception e) {
			log.info(" checkSnroom fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public int findAllNewsSnroomCount(DataMap hashMap) {
		String roleid = (String)hashMap.get("roleid");
		String userid = (String)hashMap.get("userid");
		String spcodeS=(String)hashMap.get("spcodeS");
		String spcode=(String)hashMap.get("spcode");
		String sn = (String)hashMap.get("sn");
		String roomnumber = (String)hashMap.get("roomnumber");
		String telphone = (String)hashMap.get("telphone");
		String rfloor = (String)hashMap.get("rfloor");
		 sql = "SELECT count(sn) FROM news_snroom where 1=1 ";
		if (roleid==null&&!"root".equals(userid)) {
			sql += " and spcode in (" + spcodeS + ")";
		}else if (roleid != null) {
			sql += " and spcode = '" + spcode + "' ";
		}
		if (sn != null && !"".equals(sn)) {
			sql += " and sn like '%" + sn + "%'";
		}
		if (roomnumber != null && !"".equals(roomnumber)) {
			sql += " and roomnumber like '%" + roomnumber + "%'";
		}
		if (telphone != null && !"".equals(telphone)) {
			sql += " and telphone like '%" + telphone + "%'";
		}
		if (rfloor != null && !"".equals(rfloor)) {
			sql += " and rfloor like '%" + rfloor + "%'";
		}
		int count = 0;
		try {
			log.info(" findAllNewsMsgTemplateCount excute sql: " +sql);
			count = this.getOneInt();
		} catch (Exception e) {
			log.info(" findAllNewsMsgTemplateCount fail !!!" +e.getMessage());
			e.printStackTrace();
		}
		return (count);
	}
	
	public List<NewsSnroom> findAllNewsSnroom(DataMap hashMap, int firstrow, int maxrow) {
		JsonPage jpage=(JsonPage)hashMap.get("jpage");
		String roleid = (String)hashMap.get("roleid");
		String userid = (String)hashMap.get("userid");
		String spcodeS=(String)hashMap.get("spcodeS");
		String spcode =(String)hashMap.get("spcode");
		String sn = (String)hashMap.get("sn");
		String roomnumber = (String)hashMap.get("roomnumber");
		String telphone = (String)hashMap.get("telphone");
		String rfloor = (String)hashMap.get("rfloor");
		String sql = "SELECT sn,roomnumber,telphone,rfloor FROM news_snroom where 1=1 ";
		if (roleid==null&&!"root".equals(userid)) {
			sql += " and spcode in (" + spcodeS + ")";
		}else if (roleid != null) {
			sql += " and spcode = '" + spcode + "' ";
		}
		if (sn != null && !"".equals(sn)) {
			sql += " and sn like '%" + sn + "%'";
		}
		if (roomnumber != null && !"".equals(roomnumber)) {
			sql += " and roomnumber like '%" + roomnumber + "%'";
		}
		if (telphone != null && !"".equals(telphone)) {
			sql += " and telphone like '%" + telphone + "%'";
		}
		if (rfloor != null && !"".equals(rfloor)) {
			sql += " and rfloor like '%" + rfloor + "%'";
		}
		if (jpage != null && jpage.getSidx() != null
				&& !"".equals(jpage.getSidx())) {
			sql += " order by " + jpage.getSidx();
			sql += " " + jpage.getSord();
		}
		sql += " limit " +firstrow+","+maxrow;
		List<NewsSnroom> result = new ArrayList<NewsSnroom>();
		try {
			
			log.info(" findAll NewsSnroom excute sql: " +sql);
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				NewsSnroom one = new NewsSnroom();
				one.setSn(this.getString(o[0]));
				one.setRoomnumber(this.getString(o[1]));
				one.setTelphone(this.getString(o[2]));
				one.setRfloor(this.getString(o[3]));
				result.add(one);
			}
			return result;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 根据房间号查出对应sn + 权限（spcode），此处规则：如存在多条，只取最早录入的那一条。（本表里，SN为主键，房间号是可以重复的，但某一酒店中不会重复）
	 * roomnumber可能有多个相同，但同一酒店（机构）只能有一个，不能重复。代码没有限制，录入sn与房间号关系时，需注意。
	 * @param roomnumber
	 * @return
	 */
	public String getSnByRoomnumber(DataMap hashMap) {
		String roleid = (String)hashMap.get("roleid");
		String userid = (String)hashMap.get("userid");
		String spcodeS = (String)hashMap.get("spcodeS");
		String spcode = (String)hashMap.get("spcode");
		String roomnumber = (String)hashMap.get("roomnumber");
        String sql = "select sn from news_snroom where roomnumber = '"+roomnumber+"' ";
		if (roleid==null&&!"root".equals(userid)) {
			sql += " and spcode in (" + spcodeS + ") ";
		}else if (roleid != null) {
			sql += " and spcode = '" + spcode + "' ";
		}
		sql += " limit 0,1";	//只取第一条
		log.info(" getSnByRoomnumber excute sql :" +sql);
		try {
			return this.queryOne(sql);
		} catch (Exception e) {
			log.info(" getSnByRoomnumber fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 根据id查出信息模板的模板内容
	 * @param id
	 * @return
	 */
	public String getTemplatemsgById(int id)
	{
        String sql = "select templatemsg from news_msgtemplate where id = "+id;
		log.info(" getTemplatemsgById excute sql :" +sql);
		try {
			return this.queryOne(sql);
		} catch (Exception e) {
			log.info(" getTemplatemsgById fail !!!" +e.getMessage());
			e.printStackTrace();
			return null;
		}
	}
	
	public boolean insertMsgTemplate(NewsMsgTemplate newsMsgTemplate)
	{
        String sql = "insert into news_msgtemplate (templatename,templatemsg,spcode,templatepicture) values " +
        		"('"+newsMsgTemplate.getTemplatename()+"','"+newsMsgTemplate.getTemplatemsg()+"',"+
        		newsMsgTemplate.getSpcode()+",'"+newsMsgTemplate.getTemplatepicture()+"')";
        log.info(" insert news_msgtemplate excute sql :" +sql);
		try {
			return this.execUpdate(sql);
		} catch (Exception e) {
			log.info(" insert news_msgtemplate fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean insertSnMsg(NewsSnmsg newsSnmsg)
	{
        String sql = "insert into news_snmsg (sn,msg,hassend,msgtemplateid) values " +
        		"('"+newsSnmsg.getSn()+"','"+newsSnmsg.getMsg()+"',"+newsSnmsg.getHassend()+","+newsSnmsg.getMsgtemplateid()+")";
        log.info(" insert news_snmsg excute sql :" +sql);
		try {
			return this.execUpdate(sql);
		} catch (Exception e) {
			log.info(" insert news_snmsg fail !!!" +e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public List<NewsMsgTemplate> findNewsMsgTemplateBySpcode(DataMap hashMap) {
		String roleid = (String)hashMap.get("roleid");
		String userid = (String)hashMap.get("userid");
		String spcodeS = (String)hashMap.get("spcodeS");
		String spcode = (String)hashMap.get("spcode");
		List<NewsMsgTemplate> msgTemplateList = new ArrayList<NewsMsgTemplate>();
		NewsMsgTemplate msgTemplate = null;
		String sql = "select id,templatename,templatemsg,templatepicture from news_msgtemplate ";
		if (roleid==null&&!"root".equals(userid)) {
			sql += " where spcode in (" + spcodeS + ")";
		}else if (roleid != null) {
			sql += " where spcode = '" + spcode + "' ";
		}
		try {
			List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
			for(Object[]o:tempp)
			{
				msgTemplate = new NewsMsgTemplate();
				msgTemplate.setId(this.getInteger(o[0]));
				msgTemplate.setTemplatename(this.getString(o[1]));
				msgTemplateList.add(msgTemplate);
			}
		} catch (Exception e) {
			log.info(" findNewsMsgTemplateBySpcode fail !!!" +e.getMessage());
			e.printStackTrace();
		} 
		return msgTemplateList;
	}
	
}